package com.dubbo.mapper;

import com.dubbo.domain.Shoppingcart;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import tk.mybatis.mapper.common.Mapper;

import java.util.List;
import java.util.Map;

/**
 * 购物车Mapper
 * @author wujunyuan
 * @date 2018年12月17日  上午10:46:03
 */
public interface ShoppingcartMapper extends Mapper<Shoppingcart>{

	/**
	 * 查询购物车总记录数
	 * @author wujunyuan
	 * @date 2018年12月17日  下午3:29:14
	 * @param userId 用户id
	 * @return
	 */
	@Select("SELECT" + 
			"	COUNT(os.id)" + 
			" FROM" + 
			"	o_shoppingcart os" + 
			" WHERE" + 
			"	os.userId = #{userId}")
	Long countShoppingcartByuserId(@Param("userId") String userId);

	/**
	 * 查询购物车列表
	 * @author wujunyuan
	 * @date 2018年12月17日  下午3:33:49
	 * @param userId
	 * @param pageTotal 
	 * @param pageStart 
	 * @return
	 */
	@Select("SELECT" + 
			"	os.id AS shoppingcartId," + 
			"	os.goodsId," + 
			"	os.userId," + 
			"	os.title," + 
			"	os.pic AS pic," + 
			"	os.price AS price," + 
			"	sum(os.count) AS count" + 
			" FROM" + 
			"	o_shoppingcart os" + 
			" WHERE os.userId = #{userId}"+
			" GROUP BY goodsId "+
			" ORDER BY" + 
			" CONVERT ( " + 
			"		SUBSTR(os.title, 1, 2) USING gbk " + 
			"	),"+
			"	CONVERT (" + 
			"		SUBSTR(os.title, 5, 1) USING gbk" + 
			"	) DESC," + 
			"	CONVERT (" + 
			"		SUBSTR(os.title, 7, 1) USING gbk" + 
			"	)"+
			" LIMIT #{pageStart},#{pageTotal}")
	List<Map<String, Object>> findShoppingcartList(@Param("userId") String userId,
                                                   @Param("pageStart") Long pageStart, @Param("pageTotal") Long pageTotal);
	
	/**
	 * 查询购物车列表(不分页)
	 * @author wujunyuan
	 * @date 2018年12月22日  下午2:46:40
	 * @param userId
	 * @return
	 */
	@Select("SELECT " + 
			"	os.id AS shoppingcartId, " + 
			"	os.goodsId, " + 
			"	os.userId, " + 
			"	os.title, " + 
			"	os.pic AS pic, " + 
			"	os.price AS price, " + 
			"	ggs.categoryId, " + 
			"	sum(os.count) AS count " + 
			"FROM " + 
			"	o_shoppingcart os " + 
			"	LEFT JOIN g_goods ggs ON ggs.id = os.goodsId " + 
			"WHERE " + 
			"	os.userId = #{userId} " + 
			"GROUP BY " + 
			"	goodsId " + 
			"ORDER BY " + 
			"	CONVERT ( " + 
			"		SUBSTR(os.title, 1, 2) USING gbk " + 
			"	), " + 
			"	CONVERT ( " + 
			"		SUBSTR(os.title, 5, 1) USING gbk " + 
			"	) DESC, " + 
			"	CONVERT ( " + 
			"		SUBSTR(os.title, 7, 1) USING gbk " + 
			"	)")
	List<Map<String, Object>> findShoppingcartList2(@Param("userId") String userId);
	
	/**
	 * 统计购物车商品数量和金额
	 * @author chenluqiang
	 * @date 2019年3月19日 下午4:53:43 
	 * @param userId
	 * @return
	 */
	@Select("SELECT " + 
			"	ost.count, " + 
			"	ost.price " + 
			"FROM " + 
			"	`o_shoppingcart` ost " + 
			"WHERE " + 
			"	ost.userId = #{userId}")
	List<Map<String,Object>> getShoppingCartMoneyAndNumByUserId(@Param("userId") String userId);

}